--EXEC SP_CREATE_UPDATE_PREGRIND_DISPOSE_LOT 10378,'1;2;3;','4;5;6;','1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;','SiTime','Admin'
CREATE PROCEDURE SP_CREATE_UPDATE_PREGRIND_DISPOSE_LOT (
@LOT_ID				INT,
@SORTED_WAFERS		VARCHAR(100),
@UNSORTED_WAFERS	VARCHAR(100),
@PREGRIND_WAFERS	VARCHAR(100),
@NEXT_LOCATION      VARCHAR(50),
@USER_NAME			VARCHAR(50)
)
AS 
BEGIN
	
	
	DECLARE @PROCESSSTEP_ID	INT
	DECLARE @NEW_SORT_LOT_ID INT
	DECLARE @NEW_UNSORT_LOT_ID INT
	DECLARE @NEW_LOT_LOC_ID INT
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @FAB_LOTNO	VARCHAR(50)
	DECLARE @LOT_CATEGORY	 VARCHAR(50)
	DECLARE @SORT_PROCESSSTEP_ID	INT
	DECLARE @UNSORT_PROCESSSTEP_ID	INT
	DECLARE @SORT_WAFER_IDS_AS_COMMA VARCHAR(100)
	DECLARE @UNSORT_WAFER_IDS_AS_COMMA VARCHAR(100)
	DECLARE @PREGRIND_WAFER_IDS_AS_COMMA VARCHAR(100)
	DECLARE @SORTED_WAFER_QTY INT
	DECLARE @UNSORTED_WAFER_QTY INT
	DECLARE @REMAIN_PREGRIND_WAFER_QTY INT
	DECLARE @SORT_WAFER_ID_TABLE TABLE ( WAFER_ID INT)
	DECLARE @UNSORT_WAFER_ID_TABLE TABLE( WAFER_ID INT)
	DECLARE @PREGRIND_WAFER_ID_TABLE TABLE( WAFER_ID INT)
	DECLARE @DATE_IN			DATETIME
	DECLARE @PREGRIND_REMAIN_WAFERS_STR VARCHAR(100)
	DECLARE @LOT_CURRENT_STATUS VARCHAR(20)

	SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)

	SET @SORT_WAFER_IDS_AS_COMMA = REPLACE( @SORTED_WAFERS, ';', ',' )
	SET @UNSORT_WAFER_IDS_AS_COMMA = REPLACE( @UNSORTED_WAFERS, ';', ',' )
	SET @PREGRIND_WAFER_IDS_AS_COMMA = REPLACE( @PREGRIND_WAFERS, ';', ',' )

	INSERT INTO @SORT_WAFER_ID_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SORT_WAFER_IDS_AS_COMMA )
	INSERT INTO @UNSORT_WAFER_ID_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @UNSORT_WAFER_IDS_AS_COMMA )
	INSERT INTO @PREGRIND_WAFER_ID_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PREGRIND_WAFER_IDS_AS_COMMA )

	DELETE FROM @PREGRIND_WAFER_ID_TABLE WHERE WAFER_ID IN ( SELECT WAFER_ID FROM @SORT_WAFER_ID_TABLE UNION SELECT WAFER_ID FROM @UNSORT_WAFER_ID_TABLE )

	SET @PREGRIND_REMAIN_WAFERS_STR = NULL

	SELECT @PREGRIND_REMAIN_WAFERS_STR = COALESCE( @PREGRIND_REMAIN_WAFERS_STR + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
			FROM @PREGRIND_WAFER_ID_TABLE ORDER BY WAFER_ID 
				
	SELECT @SORTED_WAFER_QTY = COUNT(*) FROM @SORT_WAFER_ID_TABLE
	SELECT @UNSORTED_WAFER_QTY = COUNT(*) FROM @UNSORT_WAFER_ID_TABLE
	SELECT @REMAIN_PREGRIND_WAFER_QTY = COUNT(*) FROM @PREGRIND_WAFER_ID_TABLE

	SELECT @NEW_LOT_LOC_ID = LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @NEXT_LOCATION AND L_TYPE = 'Wafer Store'
	
	Select @LOT_NUMBER = LOT_NUMBER, @PROCESSSTEP_ID = PROCESSSTEP_ID, @LOT_CATEGORY = LOT_CATEGORY FROM LOT_Table where Lot_id = @LOT_ID
	
	SELECT @SORT_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID, 1 ) 
			WHERE PS_TYPE = 'Sparse Probe' AND PRODUCT_NAME = (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID )

	SELECT @UNSORT_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @PROCESSSTEP_ID, 1 ) 
			WHERE PS_TYPE = 'Sparse Probe' AND PRODUCT_NAME != (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID )
	
	SELECT @NEW_SORT_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID AND LOCATION_ID = @NEW_LOT_LOC_ID AND LOT_NUMBER = @LOT_NUMBER
	SELECT @NEW_UNSORT_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @UNSORT_PROCESSSTEP_ID AND LOCATION_ID = @NEW_LOT_LOC_ID AND LOT_NUMBER = @LOT_NUMBER

	-- UPDATEING the PRE GRIND LOT WAFERIDS
	UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN - (@SORTED_WAFER_QTY+@UNSORTED_WAFER_QTY), USER_NAME = @USER_NAME WHERE LOT_ID = @LOT_ID
	UPDATE INVENTORY_LOT SET WAFER_ID = @PREGRIND_REMAIN_WAFERS_STR WHERE LOT_ID = @LOT_ID
	-- FOR SORTED WAFERS	
	IF @NEW_SORT_LOT_ID IS NULL
	BEGIN

		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_IN,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
				values (@SORT_PROCESSSTEP_ID, @NEW_LOT_LOC_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER,  @SORTED_WAFER_QTY,  @DATE_IN, 'Running', CURRENT_TIMESTAMP, 0, @LOT_CATEGORY, 0 , @USER_NAME )
		select @NEW_SORT_LOT_ID = @@IDENTITY
		insert into Inventory_Lot( Lot_ID, Wafer_ID, Timestamp ) values (@NEW_SORT_LOT_ID, @SORTED_WAFERS, CURRENT_TIMESTAMP)
	END
	ELSE
	BEGIN
		SELECT @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @NEW_SORT_LOT_ID
		IF @LOT_CURRENT_STATUS <> 'Close'
		BEGIN
			UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN + @SORTED_WAFER_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_SORT_LOT_ID
			UPDATE INVENTORY_LOT SET WAFER_ID = WAFER_ID + @SORTED_WAFERS WHERE LOT_ID = @NEW_SORT_LOT_ID
		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET Lot_Current_Status = 'Running', WAFER_QTY_IN = @SORTED_WAFER_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_SORT_LOT_ID
			UPDATE INVENTORY_LOT SET WAFER_ID = @SORTED_WAFERS WHERE LOT_ID = @NEW_SORT_LOT_ID
		END
	END
	-- FOR UNSORTED WAFERS
	IF @NEW_UNSORT_LOT_ID IS NULL
	BEGIN

		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Vendor_Lot_Number, Wafer_Qty_IN,  Date_In, Lot_Current_Status, Timestamp,Lot_Entered_By, Lot_Category, LOT_DROP_OUT, USER_NAME) 
				values (@UNSORT_PROCESSSTEP_ID, @NEW_LOT_LOC_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER,  @UNSORTED_WAFER_QTY,  @DATE_IN, 'Running', CURRENT_TIMESTAMP, 0, @LOT_CATEGORY, 0 , @USER_NAME )
		select @NEW_UNSORT_LOT_ID = @@IDENTITY
		insert into Inventory_Lot( Lot_ID, Wafer_ID, Timestamp ) values (@NEW_UNSORT_LOT_ID, @UNSORTED_WAFERS, CURRENT_TIMESTAMP)
	END
	ELSE
	BEGIN
		SELECT @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @NEW_UNSORT_LOT_ID
		IF @LOT_CURRENT_STATUS <> 'Close'
		BEGIN
			UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN + @UNSORTED_WAFER_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_UNSORT_LOT_ID
			UPDATE INVENTORY_LOT SET WAFER_ID = WAFER_ID + @UNSORTED_WAFERS WHERE LOT_ID = @NEW_UNSORT_LOT_ID
		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET Lot_Current_Status = 'Running', WAFER_QTY_IN = @UNSORTED_WAFER_QTY, USER_NAME = @USER_NAME WHERE LOT_ID = @NEW_UNSORT_LOT_ID
			UPDATE INVENTORY_LOT SET WAFER_ID = @UNSORTED_WAFERS WHERE LOT_ID = @NEW_UNSORT_LOT_ID
		END
	END
END